#!/usr/bin/env python3
"""
Directory Query Helper for UmaInsight
Provides efficient queries for directory listing and metrics
Optimized for fast page loading at /analysis/directory-list
"""

import sqlite3
import sys
import os
from typing import List, Dict, Optional, Tuple
from dataclasses import dataclass

def get_database_path() -> str:
    """Get the path to the SQLite database"""
    current_dir = os.path.dirname(os.path.abspath(__file__))
    backend_data_dir = os.path.join(current_dir, '..', 'backend', 'data')
    db_path = os.path.join(backend_data_dir, 'uma_insight.db')
    return db_path

@dataclass
class DirectoryInfo:
    """Directory information data structure"""
    id: int
    name: str
    metric_count: int
    created_at: str
    updated_at: str

@dataclass
class MetricSummary:
    """Metric summary information"""
    id: int
    name: str
    summary: Optional[str]
    team_name: Optional[str]

class DirectoryQueryHelper:
    """Helper class for directory-related queries"""

    def __init__(self):
        self.db_path = get_database_path()
        self.connection = None
        self.cursor = None

    def connect(self):
        """Connect to database"""
        self.connection = sqlite3.connect(self.db_path)
        self.cursor = self.connection.cursor()

    def close(self):
        """Close database connection"""
        if self.connection:
            self.connection.close()

    def __enter__(self):
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

    def get_all_directories(self, sort_by: str = 'metric_count', order: str = 'DESC', limit: Optional[int] = None) -> List[DirectoryInfo]:
        """Get all directories with sorting options"""
        valid_sort_fields = ['name', 'metric_count', 'created_at', 'updated_at']
        valid_orders = ['ASC', 'DESC']

        if sort_by not in valid_sort_fields:
            sort_by = 'metric_count'
        if order.upper() not in valid_orders:
            order = 'DESC'

        query = f"""
            SELECT id, name, metric_count, created_at, updated_at
            FROM directories
            WHERE metric_count > 0
            ORDER BY {sort_by} {order}
        """

        if limit:
            query += f" LIMIT {limit}"

        self.cursor.execute(query)
        results = self.cursor.fetchall()

        return [
            DirectoryInfo(
                id=row[0],
                name=row[1],
                metric_count=row[2],
                created_at=row[3],
                updated_at=row[4]
            )
            for row in results
        ]

    def get_directory_by_name(self, directory_name: str) -> Optional[DirectoryInfo]:
        """Get directory by name"""
        self.cursor.execute("""
            SELECT id, name, metric_count, created_at, updated_at
            FROM directories
            WHERE name = ?
        """, (directory_name,))

        result = self.cursor.fetchone()
        if result:
            return DirectoryInfo(
                id=result[0],
                name=result[1],
                metric_count=result[2],
                created_at=result[3],
                updated_at=result[4]
            )
        return None

    def get_metrics_in_directory(self, directory_name: str, limit: int = 50, offset: int = 0) -> List[MetricSummary]:
        """Get metrics in a specific directory with pagination"""
        query = """
            SELECT
                m.id,
                m.name,
                m.summary,
                mta.team_name
            FROM metrics m
            JOIN metric_directories md ON m.id = md.metric_id
            JOIN directories d ON md.directory_id = d.id
            LEFT JOIN metric_team_assignments mta ON m.id = mta.metric_id
            WHERE d.name = ?
            ORDER BY m.name
            LIMIT ? OFFSET ?
        """

        self.cursor.execute(query, (directory_name, limit, offset))
        results = self.cursor.fetchall()

        return [
            MetricSummary(
                id=row[0],
                name=row[1],
                summary=row[2],
                team_name=row[3]
            )
            for row in results
        ]

    def get_directory_metrics_count(self, directory_name: str) -> int:
        """Get total metrics count in a directory"""
        query = """
            SELECT COUNT(*)
            FROM metrics m
            JOIN metric_directories md ON m.id = md.metric_id
            JOIN directories d ON md.directory_id = d.id
            WHERE d.name = ?
        """

        self.cursor.execute(query, (directory_name,))
        return self.cursor.fetchone()[0]

    def search_directories(self, search_term: str, limit: int = 20) -> List[DirectoryInfo]:
        """Search directories by name"""
        query = """
            SELECT id, name, metric_count, created_at, updated_at
            FROM directories
            WHERE name LIKE ? AND metric_count > 0
            ORDER BY metric_count DESC, name
            LIMIT ?
        """

        self.cursor.execute(query, (f'%{search_term}%', limit))
        results = self.cursor.fetchall()

        return [
            DirectoryInfo(
                id=row[0],
                name=row[1],
                metric_count=row[2],
                created_at=row[3],
                updated_at=row[4]
            )
            for row in results
        ]

    def get_directory_stats_summary(self) -> Dict:
        """Get overall directory statistics summary"""
        queries = {
            'total_directories': "SELECT COUNT(*) FROM directories WHERE metric_count > 0",
            'total_metrics': "SELECT SUM(metric_count) FROM directories WHERE metric_count > 0",
            'largest_directory': "SELECT name, metric_count FROM directories WHERE metric_count > 0 ORDER BY metric_count DESC LIMIT 1",
            'avg_metrics_per_directory': "SELECT AVG(metric_count) FROM directories WHERE metric_count > 0"
        }

        results = {}
        for key, query in queries.items():
            self.cursor.execute(query)
            result = self.cursor.fetchone()
            if key == 'largest_directory':
                results[key] = {'name': result[0], 'count': result[1]} if result else None
            else:
                results[key] = result[0] if result[0] else 0

        return results

    def get_popular_directories(self, limit: int = 10) -> List[DirectoryInfo]:
        """Get most popular directories by metric count"""
        query = """
            SELECT id, name, metric_count, created_at, updated_at
            FROM directories
            WHERE metric_count > 0
            ORDER BY metric_count DESC
            LIMIT ?
        """

        self.cursor.execute(query, (limit,))
        results = self.cursor.fetchall()

        return [
            DirectoryInfo(
                id=row[0],
                name=row[1],
                metric_count=row[2],
                created_at=row[3],
                updated_at=row[4]
            )
            for row in results
        ]

def demo_queries():
    """Demonstrate the directory query helper functionality"""
    print("=== Directory Query Helper 演示 ===")

    try:
        with DirectoryQueryHelper() as helper:
            print("\n1. 获取所有Directory (按metric数量排序):")
            directories = helper.get_all_directories(limit=15)
            for i, directory in enumerate(directories[:10], 1):
                print(f"   {i:2d}. {directory.name:<20} ({directory.metric_count} metrics)")

            print(f"\n   总共 {len(directories)} 个有效directories")

            print("\n2. Directory统计摘要:")
            stats = helper.get_directory_stats_summary()
            print(f"   总Directory数量: {stats['total_directories']}")
            print(f"   总Metrics数量: {stats['total_metrics']}")
            print(f"   平均每Directory Metrics: {stats['avg_metrics_per_directory']:.1f}")
            if stats['largest_directory']:
                print(f"   最大Directory: {stats['largest_directory']['name']} ({stats['largest_directory']['count']} metrics)")

            print("\n3. 搜索'directory名称包含'Android'':")
            android_dirs = helper.search_directories('Android')
            for directory in android_dirs:
                print(f"   - {directory.name} ({directory.metric_count} metrics)")

            print("\n4. 查看'Android' Directory中的Metrics (前5个):")
            android_metrics = helper.get_metrics_in_directory('Android', limit=5)
            total_android = helper.get_directory_metrics_count('Android')
            print(f"   Android Directory总共 {total_android} metrics，显示前5个:")
            for metric in android_metrics:
                team_info = f" [{metric.team_name}]" if metric.team_name else ""
                print(f"   - {metric.name}{team_info}")

            print("\n5. 最受欢迎的Directories (Top 5):")
            popular = helper.get_popular_directories(limit=5)
            for directory in popular:
                print(f"   - {directory.name}: {directory.metric_count} metrics")

    except Exception as e:
        print(f"❌ 演示过程中出错: {e}")

if __name__ == "__main__":
    demo_queries()